package f.admin.service;

import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

import net.sf.json.JSONArray;
import net.sf.json.JSONObject;
import f.admin.entity.DictionaryModule;
import f.admin.entity.DictionaryObject;
import f.admin.entity.QueryParam;
import f.orm.dao.BaseDao;
import f.tools.DepUtil;

public class DictionaryObjectService extends BaseDao implements IService<DictionaryObject> {

	public void add(DictionaryObject dictionaryObject) throws Exception {
		String sql = "insert into " + DictionaryObject.TABLE_NAME + "(id,dictionaryModuleId,name,aliasName,description,createTime)" +
				" values(" +
						"'" + DepUtil.createPrimaryId() + "'," +
						"'" + dictionaryObject.getDictionaryModule().getId() + "'," +
						"'" + dictionaryObject.getName() + "'," +
						"'" + dictionaryObject.getAliasName() + "'," +
						"'" + dictionaryObject.getDescription() + "'," +
						"sysdate())";
		executeUpdate(sql, null);
	}

	public void update(DictionaryObject dictionaryObject) throws Exception {
		String sql = "update " + DictionaryObject.TABLE_NAME + " set name='" + dictionaryObject.getName() + "'," +
						"aliasName='" + dictionaryObject.getAliasName() + "'," +
						"description='" + dictionaryObject.getAliasName() + "', value='" + dictionaryObject.getValue() + "' where id='" + dictionaryObject.getId() + "'";
		executeUpdate(sql, null);
	}
	
	@SuppressWarnings("finally")
	public DictionaryObject getById(String id) {
		DictionaryObject dict = new DictionaryObject();
		String sql = "select * from " + DictionaryObject.TABLE_NAME + " where id = '" + id + "'";
		try {
			getConnection();
			ResultSet rs = executeQuery(sql, null);
			while(rs.next()) {
				dict.setId(rs.getString("id"));
				dict.setName(rs.getString("name"));
				dict.setAliasName(rs.getString("aliasName"));
				dict.setDescription(rs.getString("description"));
				dict.setValue(rs.getString("value"));
				dict.setCreateTime(rs.getTimestamp("createTime"));
			}
		} catch(Exception e) {
			e.printStackTrace(); 
		} finally {
			close();
			return dict;
		}
	}
	
	public boolean delete(String id) {
		boolean isDelete = false;
		String sql = "delete from " + DictionaryObject.TABLE_NAME + " where id = '" + id + "'";
		try {
			executeUpdate(sql, null);
			isDelete = true;
		} catch(Exception e) {
			isDelete = false;
			e.printStackTrace();
		}
		return isDelete;
	}
	
	@SuppressWarnings("finally")
	public List<DictionaryObject> listAll() {
		String sql = "select * from " + DictionaryObject.TABLE_NAME + " order by createTime desc";
		List<DictionaryObject> dictList = new ArrayList<DictionaryObject>();
		try {
			getConnection();
			ResultSet rs = executeQuery(sql, null);
			while(rs.next()) {
				DictionaryObject dict = new DictionaryObject();
				dict.setId(rs.getString("id"));
				dict.setName(rs.getString("name"));
				dict.setAliasName(rs.getString("aliasName"));
				dict.setDescription(rs.getString("description"));
				dict.setCreateTime(rs.getTimestamp("createTime"));
				dictList.add(dict);
			}
		} catch(Exception e) {
			e.printStackTrace(); 
		} finally {
			close();
			return dictList;
		}
	}

	@SuppressWarnings("finally")
	public List<DictionaryObject> getByQueryParams(List<QueryParam> queryParams) {
		String sql = "select * from " + DictionaryObject.TABLE_NAME + " where ";
		for(int i = 0; i < queryParams.size(); i++) {
			QueryParam queryParam = queryParams.get(i);
			sql += queryParam.getQueryParamString();
			if(i < queryParams.size() - 1) {
				sql += " and ";
			}
		}
		sql += " order by createTime desc";
		List<DictionaryObject> dictList = new ArrayList<DictionaryObject>();
		try {
			getConnection();
			ResultSet rs = executeQuery(sql, null);
			while(rs.next()) {
				DictionaryObject dict = new DictionaryObject();
				dict.setId(rs.getString("id"));
				dict.setName(rs.getString("name"));
				dict.setAliasName(rs.getString("aliasName"));
				dict.setDescription(rs.getString("description"));
				dict.setCreateTime(rs.getTimestamp("createTime"));
				dictList.add(dict);
			}
		} catch(Exception e) {
			e.printStackTrace(); 
		} finally {
			close();
			return dictList;
		}
	}
	
	@SuppressWarnings("finally")
	public List<DictionaryObject> getByQueryParam(QueryParam queryParam) {
		String sql = "select * from " + DictionaryObject.TABLE_NAME + " where ";
		sql += queryParam.getQueryParamString() + " order by createTime desc";
		List<DictionaryObject> dictList = new ArrayList<DictionaryObject>();
		try {
			getConnection();
			ResultSet rs = executeQuery(sql, null);
			while(rs.next()) {
				DictionaryObject dict = new DictionaryObject();
				dict.setId(rs.getString("id"));
				dict.setName(rs.getString("name"));
				dict.setAliasName(rs.getString("aliasName"));
				dict.setDescription(rs.getString("description"));
				dict.setCreateTime(rs.getTimestamp("createTime"));
				dictList.add(dict);
			}
		} catch(Exception e) {
			e.printStackTrace(); 
		} finally {
			close();
			return dictList;
		}
	}
	
	public List<DictionaryObject> listByDictionaryModule(String dictionaryModuleId) {
		List<DictionaryObject> dictList = getByQueryParam(new QueryParam("dictionaryModuleId", "=", dictionaryModuleId));
		return dictList;
	} 
	
	public JSONArray listToJsonArray(List<DictionaryObject> dictList) {
		JSONArray jsonArr = new JSONArray();
		for(DictionaryObject dict : dictList) {
			JSONObject jsonObj = JSONObject.fromObject(dict.getJsonString());
			jsonArr.add(jsonObj);
		}
		return jsonArr;
	}

	@SuppressWarnings("finally")
	public List<DictionaryObject> listByPage(int page, int pageSize,
			QueryParam... queryParams) {
		String sql = "select * from " + DictionaryObject.TABLE_NAME + " where ";
		for(int i = 0; i < queryParams.length; i++) {
			QueryParam queryParam = queryParams[i];
			sql += queryParam.getQueryParamString();
			if(i < queryParams.length - 1) {
				sql += " and ";
			}
		}
		sql +=  " order by createTime desc limit " + ((page - 1) * pageSize) + "," + (page * pageSize);
		List<DictionaryObject> dictList = new ArrayList<DictionaryObject>();
		try {
			getConnection();
			ResultSet rs = executeQuery(sql, null);
			while(rs.next()) {
				DictionaryObject dict = new DictionaryObject();
				dict.setId(rs.getString("id"));
				dict.setName(rs.getString("name"));
				dict.setAliasName(rs.getString("aliasName"));
				dict.setDescription(rs.getString("description"));
				dict.setCreateTime(rs.getTimestamp("createTime"));
				dictList.add(dict);
			}
		} catch(Exception e) {
			e.printStackTrace(); 
		} finally {
			close();
			return dictList;
		}
	}

	@SuppressWarnings("finally")
	public int getCount() {
		int totalCount = 0;
		String sql = "select count(id) as totalCount from " + DictionaryObject.TABLE_NAME + " order by createTime desc";
		try {
			getConnection();
			ResultSet rs = executeQuery(sql, null);
			while(rs.next()) {
				totalCount = rs.getInt("totalCount");
			}
		} catch(Exception e) {
			e.printStackTrace(); 
		} finally {
			close();
			return totalCount;
		}
	}
	
	public void addItem(String dictId, String text) throws Exception {
		DictionaryObject dictObj = getById(dictId);
		JSONArray valueItems = new JSONArray();
		if(dictObj.getValue() != null) {
			valueItems = JSONArray.fromObject(dictObj.getValue());
		}
		JSONObject tempJsonObj = new JSONObject();
		tempJsonObj.put("value", valueItems == null ? 0 : valueItems.size());
		tempJsonObj.put("text", text);
		valueItems.add(tempJsonObj);
		dictObj.setValue(valueItems.toString());
		update(dictObj);
	}
}
